package com.viettel.core.repo;

import java.io.Serializable;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.engine.spi.SessionFactoryImplementor;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.hibernate.type.Type;

import com.viettel.core.common.utils.KPaging;
import com.viettel.core.common.utils.LogUtility;
import com.viettel.core.common.utils.SpParam;
import com.viettel.core.exceptions.DataAccessException;


public class RepositoryImpl extends AbstractRepository implements IRepository {

	private SessionFactory sessionFactory;

	public RepositoryImpl(SessionFactory sessionFactory) {
		this.sessionFactory = sessionFactory;
	}

	@Override
	public <T> T create(T object) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			session.persist(object);
			return (T) object;
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}
	
	@Override
	public <T> List<T> createListObject(List<T> lstObject) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			for (Object obj : lstObject) {
				session.save(obj);
			}
			session.flush();
			session.clear();
			return lstObject;
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public void update(Object object) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			session.update(object);
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public int executeSQLQuery(String sql, List<Object> params)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			addParameters(query, params);
			return query.executeUpdate();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public void delete(Object object) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			session.delete(object);
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> T getEntityById(Class<T> clazz, Serializable id)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			return clazz.cast(session.get(clazz, id));
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> T getEntityByHQL(String hql, List<Object> params)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery(hql);
			addParameters(query, params);
			query.setCacheable(true);

			query.setMaxResults(1);
			return (T) query.uniqueResult();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> T getEntityBySQL(Class<T> clazz, String sql, List<Object> params)
			throws DataAccessException {
		return getEntityBySQL(clazz, sql, params, null);
	}

	@Override
	public <T> T getEntityBySQL(Class<T> clazz, String sql,
			List<Object> params, List<Class<?>> synchronizedClass)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			addParameters(query, params);
			addSynchronizedClass(query, synchronizedClass);
			query.setCacheable(true);
			query.addEntity(clazz);

			query.setMaxResults(1);
			return clazz.cast(query.uniqueResult());
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByHQL(String hql, List<Object> params)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			Query query = session.createQuery(hql);
			addParameters(query, params);
			query.setCacheable(true);
			return query.list();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByHQLPaginated(String selectHql, String countHql,
			List<Object> selectParams, List<Object> countParams,
			KPaging<T> paging) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();

			Query selectQuery = session.createQuery(selectHql);
			addParameters(selectQuery, selectParams);
			selectQuery.setCacheable(true);
			selectQuery.setFirstResult(paging.getPage() * paging.getPageSize());
			selectQuery.setMaxResults(paging.getPageSize());
			paging.setList(selectQuery.list());

			int totalRows = countByHQL(countHql, countParams);
			paging.setTotalRows(totalRows);

			return paging.getList();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListByHQLPaginated(String hql, List<Object> params,
			KPaging<T> paging) throws DataAccessException {
		try {
			String countHql = hql.toLowerCase().trim();
			if (countHql.startsWith("select")) {
				countHql = hql.substring(countHql.indexOf("from"));
			} else {
				countHql = hql;
			}
			countHql = "select count(*) " + countHql;
			return getListByHQLPaginated(hql, countHql, params, params, paging);
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListBySQL(Class<T> clazz, String sql,
			List<Object> params) throws DataAccessException {
		return getListBySQL(clazz, sql, params, null);
	}
	
	@Override
	public <T> List<T> getListBySQL(Class<T> clazz, String sql,
			List<Object> params, int maxResult) throws DataAccessException {
		return getListBySQL(clazz, sql, params, null, maxResult);
	}

	@Override
	public <T> List<T> getListBySQL(Class<T> clazz, String sql,
			List<Object> params, List<Class<?>> synchronizedClass)
			throws DataAccessException {
		return getListBySQL(clazz, sql, params, synchronizedClass, null);
//		try {
//			Session session = sessionFactory.getCurrentSession();
//			SQLQuery query = session.createSQLQuery(sql);
//			addParameters(query, params);
//			addSynchronizedClass(query, synchronizedClass);
//			query.setCacheable(true);
//			query.addEntity(clazz);
//			
//			return query.list();
//		} catch (Exception e) {
//			throw new DataAccessException(e);
//		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListBySQL(Class<T> clazz, String sql,
			List<Object> params, List<Class<?>> synchronizedClass, Integer maxResult)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			addParameters(query, params);
			addSynchronizedClass(query, synchronizedClass);
			query.setCacheable(true);
			query.addEntity(clazz);
			
			if (maxResult != null && maxResult > 0) {
				query.setMaxResults(maxResult);
			}

			return query.list();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListBySQLPaginated(Class<T> clazz, String selectSql,
			String countSql, List<Object> selectParams,
			List<Object> countParams, KPaging<T> paging)
			throws DataAccessException {
		return getListBySQLPaginated(clazz, selectSql, countSql, selectParams,
				countParams, paging, null);
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListBySQLPaginated(Class<T> clazz, String selectSql,
			String countSql, List<Object> selectParams,
			List<Object> countParams, KPaging<T> paging,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();

			SQLQuery selectQuery = session.createSQLQuery(selectSql);
			addParameters(selectQuery, selectParams);
			addSynchronizedClass(selectQuery, synchronizedClass);
			selectQuery.setCacheable(true);
			selectQuery.addEntity(clazz);
			selectQuery.setFirstResult(paging.getPage() * paging.getPageSize());
			selectQuery.setMaxResults(paging.getPageSize());
			paging.setList(selectQuery.list());

			int totalRows = countBySQL(countSql, countParams, synchronizedClass);
			paging.setTotalRows(totalRows);

			return paging.getList();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListBySQLPaginated(Class<T> clazz, String sql,
			List<Object> params, KPaging<T> paging) throws DataAccessException {
		return getListBySQLPaginated(clazz, sql, params, paging, null);
	}

	@Override
	public <T> List<T> getListBySQLPaginated(Class<T> clazz, String sql,
			List<Object> params, KPaging<T> paging,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			String countSql = sql.toLowerCase().trim();
			if (countSql.startsWith("select")) {
				countSql = sql.substring(countSql.indexOf("from"));
			} else {
				countSql = sql;
			}
			countSql = "select count(*) AS count " + countSql;
			return getListBySQLPaginated(clazz, sql, countSql, params, params,
					paging, synchronizedClass);
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListByQueryAndScalar(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String sql,
			List<Object> params) throws DataAccessException {
		return getListByQueryAndScalar(clazz, fieldNames, fieldTypes, sql,
				params, null, null);
	}
	
	@Override
	public <T> List<T> getListByQueryAndScalar(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String sql,
			List<Object> params, Integer maxResult) throws DataAccessException {
		return getListByQueryAndScalar(clazz, fieldNames, fieldTypes, sql,
				params, null, maxResult);
	}

	@Override
	public <T> List<T> getListByQueryAndScalar(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String sql,
			List<Object> params, List<Class<?>> synchronizedClass)
			throws DataAccessException {
		return getListByQueryAndScalar(clazz, fieldNames, fieldTypes, sql,
				params, synchronizedClass, null);
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByQueryAndScalar(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String sql,
			List<Object> params, List<Class<?>> synchronizedClass, Integer maxResult)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			addParameters(query, params);
			addScalar(query, fieldNames, fieldTypes);
			addSynchronizedClass(query, synchronizedClass);
			query.setResultTransformer(Transformers.aliasToBean(clazz));

			if (maxResult != null && maxResult > 0) {
				query.setMaxResults(maxResult);
			}
			
			return query.list();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public <T> List<T> getListByQueryAndScalarPaginated(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String selectSql,
			String countSql, List<Object> selectParams,
			List<Object> countParams, KPaging<T> paging)
			throws DataAccessException {
		return getListByQueryAndScalarPaginated(clazz, fieldNames, fieldTypes,
				selectSql, countSql, selectParams, countParams, paging, null);
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByQueryAndScalarPaginated(Class<T> clazz,
			String[] fieldNames, Type[] fieldTypes, String selectSql,
			String countSql, List<Object> selectParams,
			List<Object> countParams, KPaging<T> paging,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery selectQuery = session.createSQLQuery(selectSql);
			addParameters(selectQuery, selectParams);
			addScalar(selectQuery, fieldNames, fieldTypes);
			addSynchronizedClass(selectQuery, synchronizedClass);

			selectQuery.setResultTransformer(Transformers.aliasToBean(clazz));
			selectQuery.setFirstResult(paging.getPage() * paging.getPageSize());
			selectQuery.setMaxResults(paging.getPageSize());

			paging.setList(selectQuery.list());

			int totalRows = countBySQL(countSql, countParams, synchronizedClass);
			paging.setTotalRows(totalRows);

			return paging.getList();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	// @SuppressWarnings("unchecked")
	// @Override
	// public <T> List<T> getListByQueryAndScalarPaginated(Class<T> clazz,
	// String[] fieldNames, Type[] fieldTypes, String selectSql,
	// String countSql, List<Object> selectParams,
	// List<Object> countParams, KPaging<T> paging, CacheConfig cacheConfig) {
	// Session session = sessionFactory.getCurrentSession();
	// SQLQuery selectQuery = session.createSQLQuery(selectSql);
	// addParameters(selectQuery, selectParams);
	// addScalar(selectQuery, fieldNames, fieldTypes);
	// addCacheConfig(selectQuery, cacheConfig);
	//
	// selectQuery.setResultTransformer(Transformers.aliasToBean(clazz));
	// selectQuery.setFirstResult(paging.getPage() * paging.getPageSize());
	// selectQuery.setMaxResults(paging.getPageSize());
	//
	// paging.setList(selectQuery.list());
	//
	// int totalRows = countBySQL(countSql, countParams);
	// paging.setTotalRows(totalRows);
	//
	// return paging.getList();
	// }

	@Override
	public int countByHQL(String countHql, List<Object> countParams)
			throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			Query countQuery = session.createQuery(countHql);
			addParameters(countQuery, countParams);
			countQuery.setCacheable(true);
			return ((Number) countQuery.uniqueResult()).intValue();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@Override
	public int countBySQL(String countSql, List<Object> countParams)
			throws DataAccessException {
		return countBySQL(countSql, countParams, null);
	}

	@Override
	public int countBySQL(String countSql, List<Object> countParams,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery countQuery = session.createSQLQuery(countSql);
			addParameters(countQuery, countParams);
			addSynchronizedClass(countQuery, synchronizedClass);
			countQuery.addScalar("count", StandardBasicTypes.BIG_DECIMAL);
			countQuery.setCacheable(true);
			return ((Number) countQuery.uniqueResult()).intValue();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByNamedQuery(String namedQuerySQL,
			List<Object> params) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			Query namedQuery = session.getNamedQuery(namedQuerySQL);
			addParameters(namedQuery, params);

			namedQuery.setCacheable(true);
			return namedQuery.list();
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getListByNamedQuery(final Class<T> clazz,
			final String namedQuerySQL, final List<Object> params)
			throws DataAccessException {
		try {
			final Session session = sessionFactory.getCurrentSession();
			final Query namedQuery = session.getNamedQuery(namedQuerySQL);

			namedQuery.setResultTransformer(Transformers.aliasToBean(clazz));


			for (int i = 0; i < params.size(); i += 2) {
				namedQuery.setParameter(params.get(i).toString(), params.get(i + 1));
			}
			return namedQuery.list();

		} catch (final HibernateException e) {
			throw new DataAccessException(
					"Failed to getTopByNamedNativeQuery: " + e.getMessage(), e);
		}
	}

	@Override
	public Object getObjectByQuery(String sql, List<Object> params)
			throws DataAccessException {
		return getObjectByQuery(sql, params, null);
	}
	
	@Override
	public Object getObjectByQuery(String sql, List<Object> params,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			Session sess = sessionFactory.getCurrentSession();
			SQLQuery qSelect = sess.createSQLQuery(sql);
			addParameters(qSelect, params);
			addSynchronizedClass(qSelect, synchronizedClass);

			qSelect.setMaxResults(1);
			return qSelect.uniqueResult();

		} catch (final HibernateException e) {
			throw new DataAccessException("Failed to getObjectByQuery: "
					+ e.getMessage(), e);
		}
	}
	
	@Override
	public SessionFactory getSessionFactory() {
		return sessionFactory;
	}

	@Override
	public <T> T getFirstBySQL(Class<T> clazz, String sql, List<Object> params)
			throws DataAccessException {
		return getFirstBySQL(clazz, sql, params, null);
	}

	@Override
	public <T> T getFirstBySQL(Class<T> clazz, String sql, List<Object> params,
			List<Class<?>> synchronizedClass) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SQLQuery query = session.createSQLQuery(sql);
			addParameters(query, params);
			addSynchronizedClass(query, synchronizedClass);
			query.setCacheable(true);
			query.addEntity(clazz);
			query.setMaxResults(1);

			@SuppressWarnings("unchecked")
			List<T> list = query.list();
			
			if(list.size() > 0) {
				return list.get(0);
			} else {
				return null;
			}
		} catch (Exception e) {
			throw new DataAccessException(e);
		}
	}
	
	@SuppressWarnings("unchecked")
	@Override
	public <T> List<T> getAll(final Class<T> clazz) throws DataAccessException {
		try {
			final Criteria criteria = sessionFactory.getCurrentSession()
					.createCriteria(clazz);
			criteria.setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY);
			return criteria.list();
		} catch (HibernateException e) {
			throw new DataAccessException(e);
		}
	}
	

	@Override
	public void executeSP(final String spName) throws DataAccessException {
		try {
			Session session = sessionFactory.getCurrentSession();
			SessionFactoryImplementor sF= (SessionFactoryImplementor)session.getSessionFactory();
			Connection con = sF.getConnectionProvider().getConnection();
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("{call ");
			stringBuilder.append(spName);
			stringBuilder.append("}");
			CallableStatement st = con.prepareCall(stringBuilder.toString());

			st.executeUpdate();

		} catch (HibernateException e) {
			throw new DataAccessException(e);
		} catch (SQLException e) {
			LogUtility.logError(e, e.getMessage());
		}
	}

	@Override
	public void executeSP(final String spName, List<SpParam> inParams,
			List<SpParam> outParams) throws DataAccessException {
		try {
			int numParam;
			if (outParams == null) {
				outParams = new ArrayList<SpParam>();
			}
			if (inParams == null) {
				inParams = new ArrayList<SpParam>();
			}
			numParam = inParams.size() + outParams.size();
			
			Session session = sessionFactory.getCurrentSession();	
			SessionFactoryImplementor sF= (SessionFactoryImplementor)session.getSessionFactory();
			Connection con = sF.getConnectionProvider().getConnection();
			
			StringBuilder stringBuilder = new StringBuilder();
			stringBuilder.append("{call ");
			stringBuilder.append(spName);

			if (numParam > 0) {
				stringBuilder.append("(?");
				for (int i = 1; i < numParam; i++) {
					stringBuilder.append(", ?");
				}
				stringBuilder.append(")");
			}
			stringBuilder.append("}");
			CallableStatement st = con.prepareCall(stringBuilder.toString());
			if (inParams.size() > 0) {
				for (SpParam param : inParams) {
					setParam(st, param.getParamIndex(), param.getSqlType(),
							param.getValue());
				}
			}

			if (outParams.size() > 0) {
				for (SpParam param : outParams) {
					st.registerOutParameter(param.getParamIndex(),
							param.getSqlType());
				}
			}
			st.executeUpdate();

			for (SpParam outParam : outParams) {
				outParam.setValue(getValue(st, outParam.getParamIndex(),
						outParam.getSqlType()));
			}

		} catch (HibernateException e) {
			throw new DataAccessException(e);
		} catch (SQLException e) {
			LogUtility.logError(e, e.getMessage());
		}
	}

	private void setParam(CallableStatement st, int index, int sqlType,
			Object value) throws NumberFormatException, SQLException {

		switch (sqlType) {
		case Types.INTEGER:
			st.setInt(index, (Integer) value);
			break;

		case Types.VARCHAR:
			st.setString(index, value.toString());
			break;

		case Types.NUMERIC:
			st.setInt(index, (Integer) value);
			break;

		default:
			st.setString(index, value.toString());
			break;
		}
	}

	private Object getValue(CallableStatement st, int index, int sqlType)
			throws NumberFormatException, SQLException {

		Object result;
		switch (sqlType) {
		case Types.INTEGER:
			result = st.getInt(index);
			break;

		case Types.VARCHAR:
			result = st.getString(index);
			break;

		case Types.NUMERIC:
			result = st.getInt(index);
			break;

		default:
			result = st.getString(index);
			break;
		}
		return result;
	}
	
}
